# -*- coding: utf-8 -*-
from Dictionnaire import *
from Pickles import *
import os
os.environ['PATH'] ="C:/Oracle/Client/bin" + os.pathsep +os.environ['PATH'] 
import cx_Oracle

class BaseDonnees(object):
    def __init__(self):
        self.Erreur = ""
        self.Curseur = None
        self.Connection = None
        self.Dictionnaire = DictionnaireSQL()
        
    def DemarrerConnection(self, NomConnection,MotDePasse,NomBaseDonnee):
        try:
            print NomConnection+'/'+MotDePasse+'@'+NomBaseDonnee
            self.Connection = cx_Oracle.connect(NomConnection+'/'+MotDePasse+'@'+NomBaseDonnee)
            self.Curseur = self.Connection.cursor()
            return [1, "Connection a la base de donnees reussit"]
        except cx_Oracle.DatabaseError, erreur:
            erreurNo=erreur.args[0].code
            if self.Dictionnaire.erreurDBMsg.has_key(erreurNo):
                return [0,self.Dictionnaire.erreurDBMsg[erreurNo]]
            else:
                return [0, erreur.args[0]]
            
    def ArreterConnection(self):
        self.Curseur.close()
        self.Connection.close()
        
    def ExecuterRequeteSQL(self, RequeteSQL):
        print "Execution de ", RequeteSQL
        try:
           self.Curseur.execute(RequeteSQL)
           return 1
        except cx_Oracle.DatabaseError, erreur:
            erreurNo=erreur.args[0].code
            if self.Dictionnaire.erreurSqlMsg.has_key(erreurNo):
                self.Erreur = self.Dictionnaire.erreurSqlMsg[erreurNo]
            else: 
                self.Erreur = erreur.args[0]
            return 0
          
        
    #fonction visible au client depuis le serveur        
    def CreerTable(self, Table):
        Requete = "CREATE TABLE " + Table.Nom + " ("
        for UnChamp in Table.Champs:
            Requete = Requete + UnChamp.Nom + " " + UnChamp.Type
            if UnChamp.Taille <> None: 
                Requete = Requete + "(" + str(UnChamp.Taille) + ")"
            if UnChamp.Precision <> None:
                Requete = Requete[:-1] + "," + str(UnChamp.Precision) + ")"
            Requete = Requete + ", "
        Requete = Requete[:-2] + ")"
        if self.ExecuterRequeteSQL(Requete) == 1:
            return [1, "Creation de la table " + Table.Nom + " reussit"]
        else:
            return [0, self.Erreur]
            
    
    def ObtenirTable(self):
        Requete = "select table_name from user_tables"
        if self.ExecuterRequeteSQL(Requete) == 1:
            return [1, self.Curseur.fetchall()]
        else:
            return [0, self.Erreur]
    
    def ObtenirChamp(self, NomTable):
        Requete = "select * from " + NomTable
        if self.ExecuterRequeteSQL(Requete) == 1:
            LesChamps = []
            for UnChamp in self.Curseur.description:
                Nom = UnChamp[self.Dictionnaire.sqlCursorDescription['name']]
                TypeOracle = UnChamp[self.Dictionnaire.sqlCursorDescription['type']]
                Taille = None
                Precision = None
                Type=self.Dictionnaire.cxType[TypeOracle]
                if Type == 'VARCHAR2':
                    Taille = UnChamp[self.Dictionnaire.sqlCursorDescription['display_size']]
                if Type == 'NUMBER':
                    Taille = UnChamp[self.Dictionnaire.sqlCursorDescription['precision']]
                    Precision = UnChamp[self.Dictionnaire.sqlCursorDescription['scale']]
                    if Precision == 0:
                        Precision = None
                LesChamps.append(Champ(Nom, Type, Taille, Precision))
                LaTable = Table(NomTable, LesChamps)
            return [1, LaTable]
        else:
            return [0, self.Erreur]
    
    def EffacerTable(self, NomTable):
        Requete = "drop table " + NomTable
        if self.ExecuterRequeteSQL(Requete) == 1:
            return [1, "Table " + NomTable + " effacee"]
        else:
            return [0, self.Erreur]
    
    def EntrerEnregistrement(self, Table):
        Requete = "INSERT INTO " + Table.Nom
        Requete = Requete + "("
        for UnChamp in Table.Champs:
            Requete = Requete + UnChamp.Nom + ","
        Requete = Requete[:-1] + ") values ("
        for UnChamp in Table.Champs:
            Requete = Requete + UnChamp.Valeur + ","
        Requete = Requete[:-1] + ")"
        if self.ExecuterRequeteSQL(Requete) == 1:
            return [1, "Entrer d'un enregistrement dans la table " + Table.Nom]
        else:
            return [0, self.Erreur]
        
    def ObtenirEnregistrement(self, LaTable):
        Requete = "SELECT " 
        for UnChamp in LaTable.Champs:
            Requete = Requete + " " + UnChamp + ","
        Requete = Requete[:-1] + " FROM " + LaTable.Nom
        ListeEnregistrements = []
        if self.ExecuterRequeteSQL(Requete) == 1:
            for LesValeurs in self.Curseur.fetchall():
                ListeValeurs = []
                for UneValeur in LesValeurs:
                   ListeValeurs.append(UneValeur)
                ListeEnregistrements.append(ListeValeurs)
            LaTable = Table(LaTable.Nom, ListeEnregistrements)
            return [1, LaTable]
        else:
            return [0, self.Erreur]
        
    def Commit(self):
        Requete = "commit"
        if self.ExecuterRequeteSQL(Requete) == 1:
            return [1, "commit"]
        else:
            return [0, self.Erreur]


